package Ms

import (
	"context"
	"errors"
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"db2s/parDef"
	"db2s/ref"
	"strings"
)


func (tm TableMe) Name(ctx context.Context) (result global.Return, err error) {
	//var event = "[Name]"
	//var execSql []string
	//execSql = append(execSql, fmt.Sprintf("SELECT "+
	//	"t.name AS [tableName] \n"+
	//	"FROM \n  "+
	//	"sys.tables AS t\n "+
	//	"JOIN \n "+
	//	"sys.schemas AS s ON t.schema_id = s.schema_id \n"+
	//	"WHERE s.name = '%v' %v", parameter.Object.Schema, func() string {
	//	switch {
	//	case parameter.Object.Table == "*":
	//		return ""
	//	case strings.Contains(parameter.Object.Table, "%"):
	//		return fmt.Sprintf("and t.name like '%v'", parameter.Object.Table)
	//	default:
	//		return fmt.Sprintf("and t.name = '%v'", parameter.Object.Table)
	//	}
	//}()))
	//if result, err = forExecQuerySql(event, parameter, dataDispos.StringGarth, execSql); err != nil {
	//	err = ref.ErrAddPrintf("Name", err)
	//	return
	//}
	return
}
func (tm TableMe) Existence(ctx context.Context) (result global.Return, err error) {
	//var event = "[Existence]"
	//var execSql []string
	//execSql = append(execSql, fmt.Sprintf("SELECT \n    "+
	//	"t.name AS [TABLE_NAME]\n "+
	//	"FROM \n  "+
	//	"sys.tables AS t\n "+
	//	"JOIN \n "+
	//	"sys.schemas AS s ON t.schema_id = s.schema_id\n "+
	//	"WHERE \n "+
	//	"s.name = '%v'\n "+
	//	"AND t.name = '%v'", parameter.Object.Schema, parameter.Object.Table))
	//if result, err = forExecQuerySql(event, parameter, dataDispos.String, execSql); err != nil {
	//	err = ref.ErrAddPrintf("Existence", err)
	//	return
	//}
	//if result.Result == nil {
	//	return
	//}
	//switch {
	//case strings.EqualFold(fmt.Sprintf("%v", result.Result), parameter.Object.Table):
	//	result.Result = true
	//default:
	//	result.Result = false
	//}
	return
}
func (tm TableMe) Schema(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[Schema]"
	var execSql []string
	execSql = append(execSql, fmt.Sprintf("SELECT \n "+
		"DISTINCT  s.name AS [TABLE_SCHEMA]\n "+
		"FROM \n   "+
		"sys.tables AS t\n "+
		"JOIN \n  "+
		"sys.schemas AS s ON t.schema_id = s.schema_id\n "+
		"WHERE \n "+
		"s.name = '%v'", parameter.Object.Schema))
	if result, err = forExecQuerySql(event, parameter, ETL.String, execSql); err != nil {
		err = ref.ErrAddPrintf("Schema", err)
		return
	}
	switch {
	case strings.EqualFold(fmt.Sprintf("%v", result.Result), parameter.Object.Table):
		result.Result = true
	default:
		result.Result = false
	}
	return
}

func (tm TableMe) Comment(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[Comment]"
	var execSql []string
	execSql = append(execSql, fmt.Sprintf("SELECT \n "+
		"ep.value AS comment\n "+
		"FROM \n "+
		"sys.tables AS t\n "+
		"JOIN \n "+
		"sys.schemas AS s ON t.schema_id = s.schema_id\n "+
		"LEFT JOIN \n "+
		"sys.extended_properties AS ep ON ep.major_id = t.object_id \n "+
		"AND ep.name = 'MS_Description'\n "+
		"WHERE \n "+
		"s.name = '%v' \n "+
		"AND t.name = '%v'\n "+
		"AND ep.minor_id = 0", //minor_id = 0 为表级别comment，>0的为列级别comment
		parameter.Object.Schema, parameter.Object.Table))
	if result, err = forExecQuerySql(event, parameter, ETL.String, execSql); err != nil {
		err = ref.ErrAddPrintf("Comment", err)
		return
	}
	result.Result = stringNullActive(result.Result)
	if strings.EqualFold(fmt.Sprintf("%v", result.Result), "NULL") {
		result.Result = ""
	}
	return
}

func (tm TableMe) Character(parameter parDef.Parameter) (result global.Return, err error) {
	//var event = "[Character]"
	//var execSql []string
	//execSql = append(execSql, fmt.Sprintf("SELECT REGEXP_SUBSTR(TABLE_COLLATION, '^[^_]+') AS `character` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%v' AND TABLE_NAME='%v'", parameter.Object.Schema, parameter.Object.Table))
	//if result, err = forExecQuerySql(event, parameter, dataDispos.String, execSql); err != nil || result.Result == nil {
	//	return
	//}
	return
}
func (tm TableMe) Collation(parameter parDef.Parameter) (result global.Return, err error) {
	//var event = "[Collation]"
	//
	//var execSql []string
	//execSql = append(execSql, fmt.Sprintf("SELECT TABLE_COLLATION as `collation` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%v' AND TABLE_NAME='%v'", parameter.Object.Schema, parameter.Object.Table))
	//if result, err = forExecQuerySql(event, parameter, dataDispos.String, execSql); err != nil || result.Result == nil {
	//	return
	//}
	return
}
func (tm TableMe) Constraints(parameter parDef.Parameter) (result global.Return, err error) {
	return
}
func (tm TableMe) Drop(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[Drop]"
	defer func() {
		if re := recover(); re != nil {
			err = ref.ErrAddPrintf("SqlServer.Drop", errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
		}
	}()
	result.Sql = fmt.Sprintf("IF OBJECT_ID(N'[%v].[%v]', N'U') IS NOT NULL\n"+
		"BEGIN\n    DROP TABLE [%v].[%v];\n"+
		"END", parameter.Object.Schema, parameter.Object.Table, parameter.Object.Schema, parameter.Object.Table)
	if result.TimeOut, result.Result, err = newExecUpdateSql(ExecSqlInputS{
		Ss:           parameter,
		Event:        event,
		UpdateSqlStr: []string{result.Sql},
	}); err != nil {
		err = ref.ErrAddPrintf("SqlServer.Drop", err)
		return
	}
	return
}
func (tm TableMe) Rename(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[Rename]"
	defer func() {
		if re := recover(); re != nil {
			err = ref.ErrAddPrintf("SqlServer.Rename", errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
		}
	}()
	result.Sql = fmt.Sprintf("EXEC sp_rename '%v.%v',%v.%v", parameter.Object.Schema, parameter.Object.Table, parameter.Object.RecoverSchema, parameter.Object.RecoverTable)
	if result.TimeOut, result.Result, err = newExecUpdateSql(ExecSqlInputS{
		Ss:           parameter,
		Event:        event,
		UpdateSqlStr: []string{result.Sql},
	}); err != nil {
		err = ref.ErrAddPrintf("SqlServer..Rename", err)
		return
	}
	return
}
func (tm TableMe) Recover(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[Recover]"
	defer func() {
		if re := recover(); re != nil {
			err = ref.ErrAddPrintf("SqlServer.Recover", errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
		}
	}()
	var execSql []string
	execSql = append(execSql, fmt.Sprintf("CREATE DATABASE IF EXISTS `%v`", parameter.Object.RecoverSchema))
	execSql = append(execSql, fmt.Sprintf("SELECT * INTO [%v].[%v] FROM [%v].[%v] WHERE 1=0", parameter.Object.RecoverSchema, parameter.Object.RecoverTable, parameter.Object.Schema, parameter.Object.Table))
	execSql = append(execSql, fmt.Sprintf("EXEC sp_rename '%v.%v',%v.%v", parameter.Object.Schema, parameter.Object.Table, parameter.Object.RecoverSchema, parameter.Object.RecoverTable))
	execSql = append(execSql, fmt.Sprintf("EXEC sp_rename '%v.%v',%v.%v", parameter.Object.RecoverSchema, parameter.Object.RecoverTable, parameter.Object.Schema, parameter.Object.Table))
	if result.TimeOut, result.Result, err = newExecUpdateSql(ExecSqlInputS{
		Ss:           parameter,
		Event:        event,
		UpdateSqlStr: execSql,
	}); err != nil {
		err = ref.ErrAddPrintf("SqlServer.Recover", err)
		return
	}
	return
}
func (tm TableMe) Truncate(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[Truncate]"
	var execSql []string
	defer func() {
		if re := recover(); re != nil {
			err = ref.ErrAddPrintf("SqlServer.Truncate", errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
		}
	}()
	for _, partName := range parameter.Object.TruncatePartition {
		switch partName {
		case "single":
			execSql = append(execSql, fmt.Sprintf("TRUNCATE TABLE [%v].[%v]", parameter.Object.Schema, parameter.Object.Table))
		default:
			return
		}
	}
	if result.TimeOut, result.Result, err = newExecUpdateSql(ExecSqlInputS{
		Ss:           parameter,
		Event:        event,
		UpdateSqlStr: execSql,
	}); err != nil {
		err = ref.ErrAddPrintf("SqlServer.Truncate", err)
		return
	}
	return
}
func (tm TableMe) PartNameToShardName(parameter parDef.Parameter) (result global.Return, err error) {
	result.Result = parameter.Object.Table
	return
}
func (tm TableMe) Analyze(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[Analyze]"
	var execSql []string
	defer func() {
		if re := recover(); re != nil {
			err = ref.ErrAddPrintf("SqlServer.Analyze", errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
		}
	}()
	execSql = append(execSql, fmt.Sprintf("UPDATE STATISTICS [%v].[%v] WITH FULLSCAN", parameter.Object.Schema, parameter.Object.Table))
	if result.TimeOut, result.Result, err = newExecUpdateSql(ExecSqlInputS{
		Ss:           parameter,
		Event:        event,
		UpdateSqlStr: execSql,
	}); err != nil {
		err = ref.ErrAddPrintf("SqlServer.Analyze", err)
		return
	}
	return
}
func (tm TableMe) writeFileDDL(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[writeFileDDL]"
	defer func() {
		if re := recover(); re != nil {
			err = ref.ErrAddPrintf(event, errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
		}
	}()
	result.Sql = parameter.ExecDDL.Sql
	if parameter.ExecDDL.FilePoint == nil {
		err = ref.ErrAddPrintf(event, errors.New("file point is nil"))
		return
	}
	if err = parameter.ExecDDL.FilePoint.Write("", result.Sql); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	return
}
func (tm TableMe) connExecDDL(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[connExecDDL]"
	defer func() {
		if re := recover(); re != nil {
			err = ref.ErrAddPrintf(event, errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
		}
	}()
	result.Sql = parameter.ExecDDL.Sql
	if result.TimeOut, result.Result, err = newExecUpdateSql(ExecSqlInputS{
		Ss:           parameter,
		Event:        event,
		UpdateSqlStr: []string{result.Sql},
	}); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	return
}
func (tm TableMe) ExecDDL(ctx context.Context) (result global.Return, err error) {
	//defer func() {
	//	if re := recover(); re != nil {
	//		err = ref.ErrAddPrintf("SqlServer.ExecDDL", errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", re)))
	//	}
	//}()
	//switch parameter.Options.RepairMethod {
	//case "table":
	//	return tm.connExecDDL(parameter)
	//case "file":
	//	return tm.writeFileDDL(parameter)
	//default:
	//	err = ref.ErrAddPrintf("SqlServer.ExecDDL", errors.New(fmt.Sprintf("type not matched. curry type is %v", parameter.Options.RepairMethod)))
	//}
	return
}
func (tm TableMe) CreateGeneral(parameter parDef.Parameter) (result global.Return, err error) {
	return
}
func columnMetaBaseInit(meta any) (columnMetaSG []columnMeta, err error) {
	var event = "[columnMetaBaseInit]"
	if meta == nil {
		return
	}
	for _, v := range meta.([]map[string]any) {
		var columnMetaS columnMeta
		if err = ref.MapToStruct(v, &columnMetaS); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return
		}
		columnMetaSG = append(columnMetaSG, columnMetaS)
	}
	return
}
func generalPartRulesSql(prefix, meth string, data parDef.PartRule) (res string) {
	var partName string
	switch prefix {
	case "PARTITION":
		partName = data.Name
	case "SUBPARTITION":
		partName = data.SubName
	}
	switch strings.ToUpper(meth) {
	case "RANGE":
		res = fmt.Sprintf("%v \"%v\" VALUES LESS THAN (%v)", prefix, partName, data.Rules)
	case "LIST":
		res = fmt.Sprintf("%v \"%v\" VALUES IN (%v)", prefix, partName, data.Rules)
	}
	return
}
func generalIndexSql(schema, table string, v *parDef.IndexColumnMe) (res string) {
	var indexCol []string
	for _, vv := range v.ColumnMate {
		indexCol = append(indexCol, fmt.Sprintf("%v", vv.ColumnName))
	}
	switch v.ColumnKey {
	case "P":
		res = fmt.Sprintf("ALTER TABLE [%v].[%v] ADD CONSTRAINT [%v] PRIMARY KEY CLUSTERED ([%v])\nGO\n", schema, table, v.IndexName, strings.Join(indexCol, "],["))
	case "U":
		res = fmt.Sprintf("CREATE UNIQUE NONCLUSTERED INDEX [%v] ON [%v].[%v]([%v])\nGO\n", v.IndexName, schema, table, strings.Join(indexCol, "],["))
	case "I":
		res = fmt.Sprintf("CREATE NONCLUSTERED INDEX [%v] ON [%v].[%v]([%v])\nGO\n", v.IndexName, schema, table, strings.Join(indexCol, "],["))
	}
	return
}
func printCreateIndexSql(schema, table string, metaBase MetaBaseInitResult) (creTabIndex []string) {
	for _, v := range metaBase.indexMeta {
		creTabIndex = append(creTabIndex, generalIndexSql(schema, table, v))
	}
	return
}
func printCreatePartSql(metaBase MetaBaseInitResult) (createPartBody string) {
	var crePartRule []string
	var partRules = partRulesData(metaBase)
	var subPartRules = subPartRulesData(metaBase)
	if !strings.EqualFold(partMethData(metaBase).First, "NULL") && !strings.EqualFold(partMethData(metaBase).First, "NONE") && len(partMethData(metaBase).First) > 0 {
		createPartBody = fmt.Sprintf("PARTITION BY %v (\"%v\")", partMethData(metaBase).First, strings.Join(partColData(metaBase), "\",\""))
	}
	if !strings.EqualFold(partMethData(metaBase).Sub, "NULL") && !strings.EqualFold(partMethData(metaBase).Sub, "NONE") && len(partMethData(metaBase).Sub) > 0 {
		switch partMethData(metaBase).Sub {
		case "HASH", "KEY":
			createPartBody = fmt.Sprintf("%v SUBPARTITION BY %v (\"%v\") \n SUBPARTITIONS %v \n", createPartBody, partMethData(metaBase).Sub, strings.Join(subPartColData(metaBase), "`,`"), len(subPartRules)/len(partRules))
		default:
			createPartBody = fmt.Sprintf("%v\nSUBPARTITION BY %v (\"%v\")", createPartBody, partMethData(metaBase).Sub, strings.Join(subPartColData(metaBase), "\",\""))
		}
	}
	switch partMethData(metaBase).First {
	case "HASH", "KEY":
		crePartRule = append(crePartRule, fmt.Sprintf("PARTITIONS %v ", len(partRules)))
		createPartBody = fmt.Sprintf("%v \n %v\n", createPartBody, strings.Join(crePartRule, ",\n"))
		return
	default:
		for _, v := range partRules {
			var subPartSql []string
			for _, vv := range subPartRules {
				if strings.EqualFold(v.Name, vv.Name) {
					pq := generalPartRulesSql("SUBPARTITION", partMethData(metaBase).Sub, vv)
					if len(pq) > 0 {
						subPartSql = append(subPartSql)
					}
				}
			}
			crePartRule = append(crePartRule, fmt.Sprintf("%v %v", generalPartRulesSql("PARTITION", partMethData(metaBase).First, v), func() string {
				if len(subPartSql) > 0 {
					return fmt.Sprintf("(\n%v\n)", strings.Join(subPartSql, ",\n"))
				}
				return ""
			}()))
		}
	}
	if len(createPartBody) > 0 && len(crePartRule) > 0 {
		createPartBody = fmt.Sprintf("%v (\n%v\n)", createPartBody, strings.Join(crePartRule, ",\n"))
	}
	return
}

func printCreateSingleColumnSql(v columnMeta) (res string) {
	var null, dataType string
	switch v.IsNull {
	case "Y":
		null = "NULL"
	case "N":
		null = "NOT NULL"
	}
	dataType = fmt.Sprintf("%v", v.DataType)
	switch {
	case v.CharLength > 0:
		switch strings.ToUpper(v.ColumnType) {
		case "CHAR", "NCHAR", "VARCHAR", "NVARCHAR":
			dataType = fmt.Sprintf("%v(%v)", v.DataType, v.CharLength)
		case "BINARY", "VARBINARY", "UNIQUEIDENTIFIER":
			dataType = fmt.Sprintf("%v(%v)", v.DataType, v.CharLength)
		}
	case v.CharLength == -1 && v.DataLength == -1:
		switch strings.ToUpper(v.ColumnType) {
		case "VARCHAR", "NVARCHAR":
			dataType = fmt.Sprintf("%v(max)", v.DataType)
		}
	default:
		switch strings.ToUpper(v.ColumnType) {
		case "DECIMAL", "NUMERIC", "MONEY", "SMALLMONEY":
			if v.DataPrecision > 0 {
				dataType = fmt.Sprintf("%v(%v,%v)", v.DataType, v.DataPrecision, v.DataScale)
			}
		case "FLOAT", "REAL":
			if v.DataPrecision > 0 {
				dataType = fmt.Sprintf("%v(%v)", v.DataType, v.DataPrecision)
			}
		case "TIME", "DATETIME", "DATETIME2", "DATETIMEOFFSET":
			if v.DataScale > 0 {
				dataType = fmt.Sprintf("%v(%v)", v.DataType, v.DataScale)
			}
		}
	}
	return fmt.Sprintf("[%v] %v %v DEFAULT %v ", v.ColumnName, dataType, null, v.ColumnDefault)
}
func printCreateColumnSql(schema, table string, metaBase MetaBaseInitResult) (creTabCol []string) {
	var createTableColumn []string
	for _, v := range metaBase.columnMeta {
		createTableColumn = append(createTableColumn, printCreateSingleColumnSql(v))
	}
	creTabCol = append(creTabCol, fmt.Sprintf("\nCREATE TABLE [%v].[%v] (\n%v\n) \nGO\n", schema, table, strings.Join(createTableColumn, ",\n")))
	return
}
func printIndexBody(metaBase MetaBaseInitResult) (res []string) {
	for _, v := range metaBase.indexMeta {
		res = append(res, ref.AnyToJsonString(v))
	}
	return
}
func partMethData(metaBase MetaBaseInitResult) (meth parDef.PartMeth) {
	return metaBase.partMe.Meth
}
func partColData(metaBase MetaBaseInitResult) (partColumn []string) {
	return metaBase.partMe.PartCol
}
func subPartColData(metaBase MetaBaseInitResult) (partColumn []string) {
	return metaBase.partMe.SubPartCol
}
func partRulesData(metaBase MetaBaseInitResult) (partRules []parDef.PartRule) {
	return metaBase.partMe.RuleDesc
}
func subPartRulesData(metaBase MetaBaseInitResult) (partRules []parDef.PartRule) {
	return metaBase.partMe.SubPartDesc
}
func printPartBody(metaBase MetaBaseInitResult) (res []string) {
	res = append(res, fmt.Sprintf("part meth:%v,sub part meth:%v", partMethData(metaBase).First, partMethData(metaBase).Sub))
	res = append(res, fmt.Sprintf("part column:%v,sub part column:%v", partColData(metaBase), subPartColData(metaBase)))
	for _, v := range partRulesData(metaBase) {
		res = append(res, fmt.Sprintf("part rules:%v", ref.AnyToJsonString(v)))
	}
	for _, v := range subPartRulesData(metaBase) {
		res = append(res, fmt.Sprintf("sub part rules:%v", ref.AnyToJsonString(v)))
	}
	return
}
func printColumnBody(metaBase MetaBaseInitResult) (res []string) {
	for _, v := range metaBase.columnMeta {
		res = append(res, ref.AnyToJsonString(v))
	}
	return
}
func columnCommentCreatSql(schema, table string, metaBase MetaBaseInitResult) (creTabColComment []string) {
	for _, v := range metaBase.columnMeta {
		creTabColComment = append(creTabColComment, fmt.Sprintf("EXEC sp_addextendedproperty \n"+
			"'MS_Description',N'%v',\n"+
			"'SCHEMA', N'%v',\n"+
			"'TABLE', N'%v',\n"+
			"'COLUMN', N'%v'\n"+
			"GO\n", v.ColumnComment, schema, table, v.ColumnName))
	}
	creTabColComment = append(creTabColComment, fmt.Sprintf("EXEC sp_addextendedproperty \n"+
		"'MS_Description', N'%v',\n"+
		"'SCHEMA', N'%v',\n"+
		"'TABLE', N'%v',\n"+
		"GO\n", metaBase.Comment, schema, table))
	return
}
func (tm TableMe) CreatePrint(parameter parDef.Parameter) (result global.Return, err error) {
	var metaBase MetaBaseInitResult
	var res []string
	if metaBase, err = metaBaseInit(parameter.Meta); err != nil {
		err = ref.ErrAddPrintf("CreatePrint", err)
		return
	}
	res = append(res, printColumnBody(metaBase)...)
	res = append(res, printIndexBody(metaBase)...)
	res = append(res, printPartBody(metaBase)...)
	res = append(res, printCreateColumnSql(parameter.Object.Schema, parameter.Object.Table, metaBase)...)
	res = append(res, printCreateIndexSql(parameter.Object.Schema, parameter.Object.Table, metaBase)...)
	res = append(res, columnCommentCreatSql(parameter.Object.Schema, parameter.Object.Table, metaBase)...)
	//res = append(res, printCreatePartSql(metaBase)...)
	result.Result = strings.Join(res, "\n")
	return
}
func (tm TableMe) AlterGeneral(parameter parDef.Parameter) (result global.Return, err error) {
	return
}
